package memo.model;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


	public class MemoDAO {
		 
		private Connection con; 
		
		public static final int SUCCESS = 1;
		public static final int UNKNOWN_ERROR = -1;
		public static final int DUPLICATED_ID = -2;
		public static final int UPDATESUCCESS = -3;
		
		public static final int DUPLICATED_FOLDER = -4;
		public static final int SUCCESSFOLDER = -5;
		public static final int UPDATEFOLDERSUCCESS = -6;
		public static final int UPDATEMEMOSUCCESS = -7;
		public static final int DELETEFOLDERSUCCESS = -8;
		public static final int DELETEMEMOSUCCESS = -9;
		
		private static MemoDAO instance = new MemoDAO();  
		
		private MemoDAO() {   
		}
		
		public static MemoDAO getInstance() {
			return instance;
		}
		
		public void init(String url, String userName, String password) {   //Listener에서 initialized될 때 호출 (DB 연결)
			try {
				Class.forName("com.mysql.jdbc.Driver");  //드라이버 로딩
				con = DriverManager.getConnection(url,userName,password);
				return;
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		public void finish() {              //Listener에서 destroyed될 때 호출  (DB 해제)                    
			try {
				if(con !=null && con.isClosed()) {		
					con.close();                         //연결 해제
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		public int insertMember(MemberDTO dto) {     // DB에 회원정보 저장
			PreparedStatement pstmt = null;
			String sql=null;
			
			try {
				
				sql = "insert into member values (?, ?, ?, ?)";
				pstmt = con.prepareStatement(sql);
			
				pstmt.setString(1,dto.getId());
				pstmt.setString(2,dto.getName());
				pstmt.setString(3,dto.getPassword()); 
				pstmt.setInt(4,dto.getAge());
				pstmt.executeUpdate();			
				
			}catch(SQLException e) {
				
				e.printStackTrace();
				System.out.println(e.getErrorCode());
				
				if (e.getErrorCode() == 1062) {  //아이디 중복 에러 코드
					return DUPLICATED_ID;
				}else {
					return UNKNOWN_ERROR;
				}
			}finally{
					try {
						pstmt.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}		
			}
			return SUCCESS;
		}

		public MemberDTO getInfo(String id) {        // 회원정보 가져오기 MemberDTO에 저장 (각각의 정보 사용)
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			MemberDTO dto = new MemberDTO();
			String sql = null;
			
			try {
				sql = "select * from member where id=?";
				pstmt = con.prepareStatement(sql);
				pstmt.setString(1, id);
				rs=pstmt.executeQuery();
				
				while(rs.next()) {
					
				dto.setId(rs.getString("id"));
				dto.setPassword(rs.getString("password"));
				dto.setName(rs.getString("name"));
				dto.setAge(rs.getInt("age"));
				
				}
				
			}catch(Exception e) {
				e.printStackTrace();
			}finally{
				try {	
					rs.close();
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			return dto;
		}

		public boolean deleteMember(MemberDTO dto) {   // 회원정보 삭제
			PreparedStatement pstmt = null;
			String sql=null;
			try {
				System.out.println("dao 에서 dto.getId() : " + dto.getId());
				sql="delete from member where id=?";
				pstmt = con.prepareStatement(sql);
				pstmt.setString(1, dto.getId());
				pstmt.executeUpdate();
				
			}catch(Exception e) {
				e.printStackTrace();	
				return false;	
			}finally{
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			return true;
		}

		public int updateMember(MemberDTO dto) {   // 회원정보 수정
			PreparedStatement pstmt = null;
			String sql=null;
			try {
		
				sql = "update member set password=?, age=? where id=?";
				pstmt = con.prepareStatement(sql);
	
				pstmt.setString(1,dto.getPassword());
				pstmt.setInt(2,dto.getAge());
				pstmt.setString(3,dto.getId());
				pstmt.executeUpdate();
				
			}catch(SQLException e) {
				e.printStackTrace();
				System.out.println(e.getErrorCode());
					return UNKNOWN_ERROR;
			}finally{
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			return UPDATESUCCESS;
		
		}

		public ArrayList<MemberDTO> readMembers() {     // 회원리스트 가져오기  
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			MemberDTO dto = null;
			ArrayList<MemberDTO> list = new ArrayList<MemberDTO>();
			String sql=null;
			try {
				sql = "select * from member";
				pstmt = con.prepareStatement(sql);
				rs=pstmt.executeQuery();
				while(rs.next()) {
					dto = new MemberDTO();
					dto.setId(rs.getString("id"));
					dto.setPassword(rs.getString("password"));
					dto.setName(rs.getString("name"));
					dto.setAge(rs.getInt("age"));
					list.add(dto);
				}
				
				return list;
			}catch(Exception e) {
				e.printStackTrace();	
			}finally{
				try {
					rs.close();
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			return null;
		}
		public ArrayList<FolderDTO> getFolders(String id) {     //폴더 리스트   
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			String sql = null;
			FolderDTO dto = null;
			ArrayList<FolderDTO> list = new ArrayList<FolderDTO>();
			
			try {
				sql = "select * from folder where id=?";
				pstmt = con.prepareStatement(sql);
				pstmt.setString(1, id);
				rs=pstmt.executeQuery();
				
				while(rs.next()) {
					
				dto = new FolderDTO();
				dto.setFolderName(rs.getString("FOLDER_NAME"));
				dto.setFolderNum(rs.getInt("FOLDER_NUM"));
				dto.setId(rs.getString("id"));
				list.add(dto);
				
				}
				return list;
			}catch(Exception e) {
				e.printStackTrace();
			}finally{
				try {	
					rs.close();
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			return null;
		}
		public ArrayList<MemoDTO> getMemos(String folderName,String folderNum) {  //메모 리스트
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			String sql=null;
			MemoDTO dto = null;
			ArrayList<MemoDTO> list = new ArrayList<MemoDTO>();
			
			try {
				if (folderName.equals("전체")) {
					sql = "select * from memo";
					pstmt = con.prepareStatement(sql);
				} else {
					sql = "select * from memo where FOLDER_NUM = ?";
					pstmt = con.prepareStatement(sql);
					pstmt.setString(1, folderNum);
				}
				
				rs=pstmt.executeQuery();
				while(rs.next()) {
					dto = new MemoDTO();
					dto.setMemoNum(rs.getInt("MEMO_NUM"));
					dto.setMemoContent(rs.getString("MEMO_CONTENT"));
					list.add(dto);
				}
				
				return list;
			}catch(Exception e) {
				e.printStackTrace();	
			}finally{
				try {
					rs.close();
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			return null;
		}
		public ArrayList<MemoDTO> insertMemo(String folder_num) {         //메모 저장
			int idx = 0;
			PreparedStatement pstmt = null;
			String sql=null;
			ResultSet rs = null;
			MemoDTO Mdto = new MemoDTO();
			ArrayList<MemoDTO> list = new ArrayList<MemoDTO>();
			
			try {
				sql = "select max(MEMO_NUM) from memo";
				pstmt = con.prepareStatement(sql);
				rs=pstmt.executeQuery();
				
				if(rs.next()) {
					idx = rs.getInt(1) + 1;
				}else {
					idx = 1;
				}
				Mdto.setMemoNum(idx);
				Mdto.setMemoContent("");
				//Mdto.setFolderNum(folder_num);
				list.add(Mdto);
				pstmt.close();
				
				sql = "insert into memo(FOLDER_NUM, MEMO_CONTENT, MEMO_NUM) values (?,?,?)";
				pstmt = con.prepareStatement(sql);
			
				pstmt.setString(1, folder_num);
				pstmt.setString(2, "");
				pstmt.setInt(3, Mdto.getMemoNum());
				pstmt.executeUpdate();
				
				pstmt.close();
				return list;
				
			}catch(SQLException e) {
				
				e.printStackTrace();
				System.out.println(e.getErrorCode());
				
				
			}finally{
					try {
						pstmt.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}		
			}	
			return null;
		}
		public MemoDTO updateMemo(MemoDTO Mdto) {           //메모 수정
			PreparedStatement pstmt = null;
			String sql=null;
			try {
		
				sql = "update memo set MEMO_CONTENT=? where MEMO_NUM=?";
				pstmt = con.prepareStatement(sql);
	
				pstmt.setString(1,Mdto.getMemoContent());
				pstmt.setInt(2,Mdto.getMemoNum());
				pstmt.executeUpdate();
				
				return Mdto;
			}catch(SQLException e) {
				e.printStackTrace();
				System.out.println(e.getErrorCode());
					
			}finally{
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			return null;
		
		}
		
		public int deleteMemo(MemoDTO dto) {


			PreparedStatement pstmt = null;
			String sql=null;
			try {
		
				sql = "delete from memo where MEMO_NUM=?";
				pstmt = con.prepareStatement(sql);
				pstmt.setInt(1,dto.getMemoNum());
				pstmt.executeUpdate();
				
			}catch(SQLException e) {
				e.printStackTrace();
				System.out.println(e.getErrorCode());
					return UNKNOWN_ERROR;
			}finally{
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			return DELETEMEMOSUCCESS;

			
			
		}
		
		public FolderDTO insertFolder(FolderDTO dto) {             //폴더 생성
			int idx = 0;
			PreparedStatement pstmt = null;
			String sql = null;
			ResultSet rs = null;
		
			try {
				
				sql = "select max(FOLDER_NUM) from folder";
				pstmt = con.prepareStatement(sql);
				rs=pstmt.executeQuery();
				
				if(rs.next()) {
					idx = rs.getInt(1) + 1;
				}else {
					idx = 1;
				}
				dto.setFolderNum(idx);
				
				pstmt.close();

				sql = "insert into folder values(?,?,?)";
				pstmt = con.prepareStatement(sql);
				
				pstmt.setString(1, dto.getId());
				pstmt.setString(2, dto.getFolderName());
				pstmt.setInt(3, dto.getFolderNum());
				System.out.println(pstmt.toString());
				pstmt.executeUpdate();
				
				return dto;
			}catch(SQLException e) {
				
				e.printStackTrace();
				System.out.println(e.getErrorCode());
				
				/*if (e.getErrorCode() == 1062) {  //폴더 중복 에러 코드
					return DUPLICATED_FOLDER;
				}else {
					return UNKNOWN_ERROR;
				}*/
			}finally{
					try {
						pstmt.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}		
			}
			return null;
		}
		public int updateFolder(FolderDTO Fdto) {              //폴더 수정
			PreparedStatement pstmt = null;
			String sql=null;
			try {
		
				sql = "update folder set FOLDER_NAME=? where FOLDER_NUM=?";
				pstmt = con.prepareStatement(sql);
	
				pstmt.setString(1,Fdto.getFolderName());
				pstmt.setInt(2,Fdto.getFolderNum());
				pstmt.executeUpdate();
				
			}catch(SQLException e) {
				e.printStackTrace();
				System.out.println(e.getErrorCode());
					return UNKNOWN_ERROR;
			}finally{
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			return UPDATEFOLDERSUCCESS;
		
		}
		
		public int deleteFolder(FolderDTO dto) {
			
			PreparedStatement pstmt = null;
			String sql=null;
			try {
		
				sql = "delete from folder where FOLDER_NUM=?";
				pstmt = con.prepareStatement(sql);
				pstmt.setInt(1,dto.getFolderNum());
				pstmt.executeUpdate();
				
			}catch(SQLException e) {
				e.printStackTrace();
				System.out.println(e.getErrorCode());
					return UNKNOWN_ERROR;
			}finally{
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			return DELETEFOLDERSUCCESS;
			
	
		}

		
		
	}

